using Microsoft.Extensions.Logging;
using SeataNet.Core.AT.UndoLog;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;

namespace SeataNet.Core.Database.SqlServer
{
    /// <summary>
    /// SQL Server数据库方言
    /// </summary>
    public class SqlServerDialect : IDatabaseDialect
    {
        private readonly ILogger<SqlServerDialect> _logger;

        public SqlServerDialect(ILogger<SqlServerDialect> logger)
        {
            _logger = logger;
        }

        public DatabaseType DatabaseType => DatabaseType.SqlServer;

        public async Task<TableMeta> GetTableMetaAsync(DbConnection connection, string tableName)
        {
            _logger.LogDebug($"获取SQL Server表元数据: {tableName}");

            var tableMeta = new TableMeta
            {
                TableName = tableName
            };

            // 获取列信息
            var columnsSql = @"
                SELECT
                    c.COLUMN_NAME,
                    c.DATA_TYPE,
                    c.IS_NULLABLE,
                    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END as COLUMN_KEY
                FROM INFORMATION_SCHEMA.COLUMNS c
                LEFT JOIN (
                    SELECT ku.COLUMN_NAME
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
                        ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
                        AND tc.TABLE_SCHEMA = ku.TABLE_SCHEMA
                        AND tc.TABLE_NAME = ku.TABLE_NAME
                    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
                        AND tc.TABLE_NAME = @TableName
                ) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
                WHERE c.TABLE_NAME = @TableName
                ORDER BY c.ORDINAL_POSITION";

            using (var command = connection.CreateCommand())
            {
                command.CommandText = columnsSql;
                var param = command.CreateParameter();
                param.ParameterName = "@TableName";
                param.Value = tableName;
                command.Parameters.Add(param);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var columnName = reader.GetString(0);
                        var dataType = reader.GetString(1);
                        var isNullable = reader.GetString(2) == "YES";
                        var columnKey = reader.IsDBNull(3) ? "" : reader.GetString(3);
                        var isPrimaryKey = columnKey == "PRI";

                        tableMeta.Columns[columnName] = new ColumnMeta
                        {
                            ColumnName = columnName,
                            DataType = dataType,
                            IsNullable = isNullable,
                            IsPrimaryKey = isPrimaryKey
                        };

                        if (isPrimaryKey)
                        {
                            tableMeta.PrimaryKeyColumns.Add(columnName);
                        }
                    }
                }
            }

            _logger.LogDebug($"SQL Server表元数据加载完成: {tableName}, 列数: {tableMeta.Columns.Count}, 主键: {string.Join(", ", tableMeta.PrimaryKeyColumns)}");

            return tableMeta;
        }

        public string BuildBeforeImageSql(SqlRecognizeResult recognizeResult, TableMeta tableMeta)
        {
            if (recognizeResult.SqlType == SqlType.INSERT)
            {
                // INSERT操作不需要before image
                return null;
            }

            var columns = string.Join(", ", tableMeta.Columns.Keys.Select(c => $"[{c}]"));
            var tableName = $"[{recognizeResult.TableName}]";

            if (recognizeResult.SqlType == SqlType.UPDATE || recognizeResult.SqlType == SqlType.DELETE)
            {
                // 使用原SQL的WHERE条件，SQL Server使用UPDLOCK提示
                var whereClause = recognizeResult.WhereCondition ?? "1=1";
                return $"SELECT {columns} FROM {tableName} WITH (UPDLOCK) WHERE {whereClause}";
            }

            return null;
        }

        public string BuildAfterImageSql(SqlRecognizeResult recognizeResult, TableMeta tableMeta)
        {
            if (recognizeResult.SqlType == SqlType.DELETE)
            {
                // DELETE操作不需要after image
                return null;
            }

            var columns = string.Join(", ", tableMeta.Columns.Keys.Select(c => $"[{c}]"));
            var tableName = $"[{recognizeResult.TableName}]";

            if (recognizeResult.SqlType == SqlType.INSERT || recognizeResult.SqlType == SqlType.UPDATE)
            {
                // 使用主键条件或WHERE条件
                if (!string.IsNullOrEmpty(recognizeResult.WhereCondition))
                {
                    return $"SELECT {columns} FROM {tableName} WHERE {recognizeResult.WhereCondition}";
                }
                else if (tableMeta.PrimaryKeyColumns.Any())
                {
                    // 对于INSERT，SQL Server可以使用OUTPUT子句，但这里返回基础查询
                    // 实际使用时需要添加主键条件
                    return $"SELECT {columns} FROM {tableName} WHERE 1=1";
                }
            }

            return null;
        }

        public ISqlRecognizer GetSqlRecognizer(string sql)
        {
            return new SqlServerRecognizer(sql, _logger);
        }
    }

    /// <summary>
    /// SQL Server SQL识别器
    /// </summary>
    public class SqlServerRecognizer : ISqlRecognizer
    {
        private readonly string _originalSql;
        private readonly ILogger _logger;
        private SqlRecognizeResult _result;

        public SqlServerRecognizer(string sql, ILogger logger)
        {
            _originalSql = sql?.Trim();
            _logger = logger;
        }

        public SqlType GetSqlType()
        {
            if (string.IsNullOrEmpty(_originalSql))
            {
                return SqlType.SELECT_FOR_UPDATE;
            }

            var upperSql = _originalSql.ToUpperInvariant();

            if (upperSql.StartsWith("INSERT"))
                return SqlType.INSERT;
            if (upperSql.StartsWith("UPDATE"))
                return SqlType.UPDATE;
            if (upperSql.StartsWith("DELETE"))
                return SqlType.DELETE;

            return SqlType.SELECT_FOR_UPDATE;
        }

        public string GetTableName()
        {
            var sqlType = GetSqlType();

            try
            {
                switch (sqlType)
                {
                    case SqlType.INSERT:
                        // INSERT INTO table_name or INSERT INTO [table_name]
                        var insertMatch = Regex.Match(_originalSql, @"INSERT\s+INTO\s+\[?(\w+)\]?", RegexOptions.IgnoreCase);
                        return insertMatch.Success ? insertMatch.Groups[1].Value : null;

                    case SqlType.UPDATE:
                        // UPDATE table_name or UPDATE [table_name]
                        var updateMatch = Regex.Match(_originalSql, @"UPDATE\s+\[?(\w+)\]?", RegexOptions.IgnoreCase);
                        return updateMatch.Success ? updateMatch.Groups[1].Value : null;

                    case SqlType.DELETE:
                        // DELETE FROM table_name or DELETE FROM [table_name]
                        var deleteMatch = Regex.Match(_originalSql, @"DELETE\s+FROM\s+\[?(\w+)\]?", RegexOptions.IgnoreCase);
                        return deleteMatch.Success ? deleteMatch.Groups[1].Value : null;

                    default:
                        return null;
                }
            }
            catch (Exception ex)
            {
                _logger?.LogWarning(ex, $"解析表名失败: {_originalSql}");
                return null;
            }
        }

        public SqlRecognizeResult Recognize()
        {
            if (_result != null)
            {
                return _result;
            }

            _result = new SqlRecognizeResult
            {
                OriginalSql = _originalSql,
                SqlType = GetSqlType(),
                TableName = GetTableName()
            };

            try
            {
                switch (_result.SqlType)
                {
                    case SqlType.INSERT:
                        RecognizeInsert();
                        break;

                    case SqlType.UPDATE:
                        RecognizeUpdate();
                        break;

                    case SqlType.DELETE:
                        RecognizeDelete();
                        break;
                }
            }
            catch (Exception ex)
            {
                _logger?.LogWarning(ex, $"SQL识别失败: {_originalSql}");
            }

            return _result;
        }

        private void RecognizeInsert()
        {
            // INSERT INTO table ([col1], [col2]) VALUES (?, ?)
            var match = Regex.Match(_originalSql, @"INSERT\s+INTO\s+\[?\w+\]?\s*\(([^)]+)\)", RegexOptions.IgnoreCase);
            if (match.Success)
            {
                var columnsStr = match.Groups[1].Value;
                _result.InsertColumns = columnsStr
                    .Split(',')
                    .Select(c => c.Trim().Trim('[', ']'))
                    .ToList();
            }
        }

        private void RecognizeUpdate()
        {
            // UPDATE table SET [col1] = ?, [col2] = ? WHERE condition

            // 提取SET子句的列
            var setMatch = Regex.Match(_originalSql, @"SET\s+(.+?)(?:WHERE|$)", RegexOptions.IgnoreCase);
            if (setMatch.Success)
            {
                var setClause = setMatch.Groups[1].Value;
                var assignments = Regex.Matches(setClause, @"\[?(\w+)\]?\s*=");

                foreach (Match assignment in assignments)
                {
                    _result.UpdateColumns.Add(assignment.Groups[1].Value);
                }
            }

            // 提取WHERE子句
            var whereMatch = Regex.Match(_originalSql, @"WHERE\s+(.+)$", RegexOptions.IgnoreCase);
            if (whereMatch.Success)
            {
                _result.WhereCondition = whereMatch.Groups[1].Value.Trim();
            }
        }

        private void RecognizeDelete()
        {
            // DELETE FROM table WHERE condition
            var whereMatch = Regex.Match(_originalSql, @"WHERE\s+(.+)$", RegexOptions.IgnoreCase);
            if (whereMatch.Success)
            {
                _result.WhereCondition = whereMatch.Groups[1].Value.Trim();
            }
        }
    }
}
